Understanding Relational Databases and SQL Fundamentals

Course: Python Training

Topic: Understanding Relational Databases and SQL Fundamentals

Course Link: Python Training Course at JustAcademy

Demo Registration: Register for a Course Demo

JustAcademy Homepage: Visit JustAcademy

1. Introduction to Relational Databases

A relational database is a structured system for storing, organizing, retrieving, and managing data in tables. Each table represents a particular entity or subject, such as customers, products, employees, or orders. Tables contain rows and columns that help organize information consistently.

Relational databases are widely used in business applications, banking systems, e-commerce platforms, healthcare systems, educational portals, inventory applications, and websites. They provide ways to connect related information, maintain data integrity, and retrieve records efficiently.

For example, an online shopping application might store customer information in a Customers table, product details in a Products table, and purchases in an Orders table. These tables can be connected through keys to show which customer placed each order.

Example of a Relational Table

In this table, each row represents a student, each column represents an attribute, and StudentID uniquely identifies a student.

2. Learning Objectives

After completing this topic, learners should be able to:

• Explain relational databases and their purpose.
• Understand tables, rows, columns, records, and fields.
• Describe the role of a Database Management System (DBMS).
• Understand primary keys, foreign keys, and relationships.
• Differentiate SQL from a database management system.
• Create databases and tables using SQL statements.
• Insert, retrieve, update, and delete records.
• Filter, sort, and group data using SQL queries.
• Understand constraints, normalization, and referential integrity.
• Connect relational database concepts to Python applications.

3. What Is a Database?

A database is an organized collection of related data. It allows information to be stored and retrieved in a structured way rather than scattered across unrelated files or documents.

Consider a college database. It may store students, instructors, courses, attendance, examinations, and fees. Instead of maintaining all information in one large table, a relational database can separate the information into related tables.

Common Types of Databases

This topic focuses on relational databases and SQL, which are foundational skills for Python developers working with persistent application data.

4. Understanding a Relational Database Management System

A Relational Database Management System (RDBMS) is software that manages relational databases. It provides tools for creating tables, storing records, querying information, enforcing constraints, managing transactions, and controlling access.

Popular Relational Database Systems

Different systems support different features, data types, functions, and SQL extensions. SQL concepts are broadly transferable, but syntax and behavior can vary between database products.

5. Fundamental Components of Relational Tables

5.1 Table

A table is a collection of related records organized into rows and columns. For example, an Employees table stores information about employees.

5.2 Row or Record

A row represents one individual record in a table. In an employee table, a row might contain one employee's ID, name, department, and salary.

5.3 Column or Field

A column represents an attribute shared by all records in a table. Examples include EmployeeID, Name, Department, and Salary.

5.4 Schema

A schema describes the structure of a database, including its tables, columns, data types, constraints, and relationships. Some database systems also use the term schema for a namespace that groups database objects.

5.5 Data Type

A data type defines the kind of values that a column can store. Examples include integers, character strings, decimal numbers, dates, and Boolean values, depending on the database system.

Example Employee Table

Here, the table is named Employees, the columns describe employee attributes, and each row contains one employee record.

6. What Is SQL?

SQL stands for Structured Query Language. It is the standard language commonly used to define, query, modify, and control data in relational databases.

SQL allows developers to communicate with a database without manually searching through its stored files. A SQL statement specifies an operation, and the database engine processes that statement according to its rules and capabilities.

Common Uses of SQL

• Create databases, tables, and other database objects.
• Insert new records into tables.
• Retrieve data using conditions and sorting.
• Update existing records.
• Delete records that are no longer required.
• Combine data from multiple tables.
• Aggregate data using functions such as COUNT(), SUM(), and AVG().
• Manage transactions and enforce data integrity.

Example SQL Query

SELECT EmployeeName, Department
FROM Employees;

This query retrieves the employee name and department columns from the Employees table.

7. SQL Command Categories

SQL statements are often grouped into categories according to their purpose. The exact classification can vary slightly between learning materials and database systems.

8. Creating a Database

The CREATE DATABASE statement creates a new database in systems that support this command.

CREATE DATABASE CollegeDB;

This statement requests the creation of a database named CollegeDB. Depending on the database system, permissions and connection settings, additional steps may be needed to select or connect to the new database.

Selecting a Database

In MySQL, you can select the database with the following statement:

USE CollegeDB;

The USE statement is supported by MySQL and some other systems, but not by every relational database. For example, PostgreSQL commonly selects the target database through the connection configuration rather than using USE.

9. Creating Tables with CREATE TABLE

The CREATE TABLE statement defines a table's structure. It specifies column names, data types, and optional constraints.

Example: Creating a Students Table

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Age INT,
    Course VARCHAR(50),
    Email VARCHAR(150) UNIQUE
);

This table contains five columns. StudentID is the primary key, StudentName is required, and Email must be unique when a non-NULL email value is provided. The precise behavior of unique constraints involving NULL values can differ between database systems.

Common SQL Data Types

Choose data types based on the information being stored. For example, use DECIMAL for monetary amounts that require exact decimal arithmetic rather than relying on approximate floating-point storage.

10. Understanding Primary Keys

A primary key uniquely identifies each row in a table. A primary key must be unique and cannot contain NULL values.

For example, two students may share the same name, but their student IDs should be different. A student ID is therefore a better choice for a primary key than a name.

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    DurationMonths INT
);

Characteristics of a Primary Key

• Every row must have a unique primary-key value.
• Primary-key columns cannot contain NULL values.
• A table has one primary-key constraint, which can consist of one or multiple columns.
• Primary keys help other tables refer to a particular record.
• Primary-key values should be managed consistently because other records may depend on them.

11. Understanding Foreign Keys

A foreign key connects a column or group of columns in one table to a candidate key, commonly the primary key, in another table. It helps maintain referential integrity by preventing references to nonexistent parent records, subject to the database's configured rules.

Example: Students and Enrollments

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT NOT NULL,
    CourseName VARCHAR(100) NOT NULL,
    FOREIGN KEY (StudentID)
        REFERENCES Students(StudentID)
);

The Enrollments table refers to Students through StudentID. A student can have multiple enrollment records, while each enrollment record references a student.

Primary Key vs Foreign Key

12. Understanding Database Relationships

Relationships describe how records in different tables relate to one another.

12.1 One-to-One Relationship

Each record in one table corresponds to at most one record in another table. For example, a person might have one profile record in a system. A one-to-one relationship is commonly implemented using a foreign key with a unique constraint.

12.2 One-to-Many Relationship

One record in a parent table can be related to multiple records in a child table. For example, one department can have many employees. The foreign key is usually stored in the many-side table.

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID)
        REFERENCES Departments(DepartmentID)
);

12.3 Many-to-Many Relationship

Multiple records in one table can be related to multiple records in another table. For example, students can enroll in many courses, and each course can contain many students.

A many-to-many relationship is typically implemented through a junction table.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL
);

CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

The composite primary key prevents the same student-course combination from being inserted more than once.

13. Inserting Data with INSERT

The INSERT INTO statement adds new records to a table. Listing the target columns explicitly is a good practice because it makes the query clearer and less dependent on the table's column order.

INSERT INTO Students
    (StudentID, StudentName, Age, Course, Email)
VALUES
    (101, 'Aarav Sharma', 22, 'Python', '[email protected]'),
    (102, 'Diya Patel', 24, 'SQL', '[email protected]'),
    (103, 'Rohan Mehta', 21, 'Python', '[email protected]');

This example inserts three student records. The multi-row INSERT syntax is supported by common database systems, although exact syntax may vary.

Inserting a Single Record

INSERT INTO Students
    (StudentID, StudentName, Age, Course, Email)
VALUES
    (104, 'Sara Khan', 23, 'Database Design', '[email protected]');

Values must match the target columns' data types and satisfy any relevant constraints.

14. Retrieving Data with SELECT

The SELECT statement retrieves data from one or more tables. It is one of the most frequently used SQL statements.

Select All Columns

SELECT *
FROM Students;

The asterisk selects all columns from the table. In production queries, explicitly listing required columns is often preferable because it improves readability and reduces unnecessary data transfer.

Select Specific Columns

SELECT StudentName, Course
FROM Students;

Using Column Aliases

An alias provides a temporary, readable name for a column or expression in query output.

SELECT
    StudentName AS Name,
    Course AS EnrolledCourse
FROM Students;

Aliases affect the output labels of the query; they do not rename the stored columns.

15. Filtering Records with WHERE

The WHERE clause filters records based on a condition. Only rows that satisfy the condition are returned by a SELECT query.

Example: Filtering by Course

SELECT StudentName, Course
FROM Students
WHERE Course = 'Python';

Common Comparison Operators

Combining Conditions

The AND operator requires both conditions to be true. The OR operator requires at least one condition to be true. The NOT operator reverses a condition.

SELECT *
FROM Students
WHERE Age >= 22 AND Course = 'Python';

SELECT *
FROM Students
WHERE Course = 'Python' OR Course = 'SQL';

SELECT *
FROM Students
WHERE NOT Course = 'SQL';

Use parentheses when combining multiple conditions to make the intended logic explicit.

16. Using BETWEEN, IN, LIKE, and NULL

16.1 BETWEEN

The BETWEEN operator checks whether a value falls within an inclusive range.

SELECT *
FROM Students
WHERE Age BETWEEN 21 AND 23;

This condition includes values equal to 21 and 23.

16.2 IN

The IN operator checks whether a value matches one of the values in a list.

SELECT *
FROM Students
WHERE Course IN ('Python', 'SQL');

16.3 LIKE

The LIKE operator searches character data using pattern matching. The percent sign (%) matches zero or more characters, and the underscore (_) matches one character in standard SQL pattern matching.

SELECT *
FROM Students
WHERE StudentName LIKE 'A%';

SELECT *
FROM Students
WHERE StudentName LIKE '_i%';

Case sensitivity and pattern-matching behavior depend on the database system and collation settings.

16.4 Working with NULL Values

NULL represents missing, unknown, or unavailable information. It is not the same as zero or an empty string.

Use IS NULL or IS NOT NULL to check for NULL values.

SELECT *
FROM Students
WHERE Email IS NULL;

SELECT *
FROM Students
WHERE Email IS NOT NULL;

Do not use Email = NULL to test for missing values. SQL uses three-valued logic, so comparisons involving NULL generally produce an unknown result rather than ordinary true or false.

17. Sorting Results with ORDER BY

The ORDER BY clause sorts query results by one or more columns.

Ascending Order

SELECT StudentName, Age
FROM Students
ORDER BY Age ASC;

Descending Order

SELECT StudentName, Age
FROM Students
ORDER BY Age DESC;

Sorting by Multiple Columns

SELECT StudentName, Course, Age
FROM Students
ORDER BY Course ASC, Age DESC;

In this example, students are sorted by course first and by age in descending order within each course. If consistent ordering among tied rows is important, include an additional unique column in the sort criteria.

18. Limiting Query Results

Applications often need only a small number of records, such as the first ten products or the most recent five orders.

Using LIMIT

SELECT *
FROM Students
ORDER BY StudentID
LIMIT 5;

LIMIT is supported by MySQL, PostgreSQL, SQLite, and several other systems. SQL Server commonly uses TOP or OFFSET ... FETCH, depending on the query.

Example with OFFSET

SELECT *
FROM Students
ORDER BY StudentID
LIMIT 5 OFFSET 10;

This query skips the first ten ordered records and returns up to five records. Pagination should use a stable sort order to reduce inconsistent results between pages.

19. Updating Records with UPDATE

The UPDATE statement changes existing records. The WHERE clause is especially important because it determines which rows are affected.

UPDATE Students
SET Course = 'Advanced Python'
WHERE StudentID = 101;

This statement changes the course value only for the student whose ID is 101.

Updating Multiple Columns

UPDATE Students
SET Course = 'SQL Fundamentals',
    Age = 25
WHERE StudentID = 102;

Important: An UPDATE statement without a WHERE clause generally updates every row in the table. Verify the condition and, for sensitive operations, consider using a transaction and checking the affected-row count.

20. Deleting Records with DELETE

The DELETE statement removes rows from a table. A condition can be used to identify the records that should be removed.

DELETE FROM Students
WHERE StudentID = 104;

This statement removes the record with ID 104, subject to any foreign-key rules that may affect the operation.

A DELETE statement without a WHERE clause can remove all rows from a table. Before deleting data, verify the target records and follow the application's backup and transaction policies.

DELETE vs DROP vs TRUNCATE

21. Aggregate Functions

Aggregate functions summarize multiple rows and return a calculated result. They are frequently used in reports, dashboards, and data analysis.

Example: Counting Students

SELECT COUNT(*) AS TotalStudents
FROM Students;

Example: Finding the Average Age

SELECT AVG(Age) AS AverageAge
FROM Students;

Example: Minimum and Maximum Age

SELECT
    MIN(Age) AS MinimumAge,
    MAX(Age) AS MaximumAge
FROM Students;

Most aggregate functions ignore NULL values in the expression being aggregated. COUNT(*), however, counts rows regardless of whether individual columns contain NULL.

22. Grouping Records with GROUP BY

The GROUP BY clause groups rows with matching values so aggregate functions can calculate summaries for each group.

Example: Number of Students per Course

SELECT Course, COUNT(*) AS TotalStudents
FROM Students
GROUP BY Course;

Example: Average Age per Course

SELECT Course, AVG(Age) AS AverageAge
FROM Students
GROUP BY Course;

Every selected expression in a grouped query must follow the database system's grouping rules. Typically, selected columns must be grouped or used inside an aggregate function.

23. Filtering Groups with HAVING

The HAVING clause filters groups after grouping and aggregation. It is often used when a condition depends on an aggregate result.

SELECT Course, COUNT(*) AS TotalStudents
FROM Students
GROUP BY Course
HAVING COUNT(*) > 2;

This query returns only courses with more than two students.

WHERE vs HAVING

24. Introduction to SQL Joins

A join combines related data from two or more tables. Joins are essential in relational databases because information is commonly separated into tables to reduce duplication and represent relationships clearly.

24.1 INNER JOIN

An INNER JOIN returns rows that match the join condition in both tables.

SELECT
    Students.StudentName,
    Enrollments.CourseName
FROM Students
INNER JOIN Enrollments
    ON Students.StudentID = Enrollments.StudentID;

24.2 LEFT JOIN

A LEFT JOIN returns all rows from the left table and matching rows from the right table. If no matching right-side row exists, the right-side columns contain NULL values.

SELECT
    Students.StudentName,
    Enrollments.CourseName
FROM Students
LEFT JOIN Enrollments
    ON Students.StudentID = Enrollments.StudentID;

24.3 RIGHT JOIN

A RIGHT JOIN returns all rows from the right table and matching rows from the left table. Unmatched left-side columns contain NULL values. Support and alternatives can vary across database products.

24.4 CROSS JOIN

A CROSS JOIN produces the Cartesian product of two tables, pairing every row from the first table with every row from the second table. It can produce a very large result when either table contains many rows.

Join Comparison

25. Database Constraints

Constraints are rules enforced by the database to help maintain accurate and consistent data.

Example: Table with Constraints

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0),
    StockQuantity INT NOT NULL DEFAULT 0
);

Constraints reduce the chance of invalid data entering a database. The exact availability and enforcement of certain constraint features depend on the database engine and version.

26. Introduction to Database Normalization

Normalization is the process of organizing data into related tables to reduce unnecessary duplication and prevent common data anomalies.

For example, storing a student's details repeatedly in every course enrollment can create inconsistencies. Separating student information from enrollment records allows the student information to be stored in one place.

First Normal Form (1NF)

First Normal Form requires each field to contain a single value from its intended domain and avoids repeating groups of columns. A column should not store a list of unrelated values that need to be searched or updated individually.

Second Normal Form (2NF)

Second Normal Form requires a table to satisfy 1NF and prevents non-key attributes from depending on only part of a candidate key. This is particularly relevant to tables with composite keys.

Third Normal Form (3NF)

Third Normal Form requires a table to satisfy 2NF and addresses problematic transitive dependencies of non-key attributes on candidate keys. It helps keep facts about separate entities in appropriate tables.

Benefits of Normalization

• Reduces unnecessary data duplication.
• Helps prevent inconsistent updates.
• Improves clarity in data relationships.
• Supports reliable inserts, updates, and deletes.
• Makes database structures easier to maintain.

Normalization should be balanced against application requirements. Some analytical systems deliberately use denormalized structures to improve reporting performance, but such choices should be made with a clear understanding of their trade-offs.

27. Transactions and ACID Properties

A transaction is a logical unit of database work. It may contain one or more statements that should be handled together according to the database's transaction rules.

For example, transferring money between two accounts involves deducting an amount from one account and adding it to another. The application must avoid leaving the accounts inconsistent if part of the operation fails.

ACID Properties

Transaction Commands

BEGIN;

UPDATE Accounts
SET Balance = Balance - 500
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 500
WHERE AccountID = 2;

COMMIT;

This is a simplified illustration. Actual transaction-start syntax and transaction behavior vary by database system and driver. A production implementation should verify that both accounts exist, that the first account has sufficient funds, and that both updates succeed before committing. If any required step fails, the application should roll back the transaction.

28. Indexes and Query Performance

An index is a database structure that can help the database find records without scanning every row of a table. Indexes are commonly used on columns frequently searched, joined, or sorted.

Creating an Index

CREATE INDEX idx_students_course
ON Students(Course);

This statement creates an index on the Course column. It may improve some queries that filter or sort by course, depending on the data distribution and database query planner.

Advantages of Indexes

• Can speed up selective searches.
• Can improve join and ordering operations.
• May help enforce uniqueness when implemented as a unique index.

Disadvantages of Indexes

• Require additional storage.
• Can slow down inserts, updates, and deletes because indexes may need maintenance.
• Do not automatically improve every query.

Use query execution plans and performance measurements to determine whether an index is useful. Avoid creating indexes indiscriminately.

29. SQL Security and Safe Query Practices

Database security protects sensitive information and prevents unauthorized access or modification.

• Least privilege: Give each database account only the permissions required for its tasks.
• Parameterized queries: Pass user input as parameters rather than concatenating it into SQL strings.
• Validate inputs: Apply application-level validation in addition to database constraints.
• Protect credentials: Keep database passwords and connection secrets out of public source code.
• Use transactions: Group related changes when they must succeed or fail together.
• Back up data: Follow tested backup and recovery procedures.
• Audit access: Apply appropriate logging and monitoring for sensitive systems.

Unsafe Query Construction

# Unsafe pattern: do not build SQL with user input.
query = "SELECT * FROM Users WHERE username = '" + username + "'"

Concatenating untrusted input into a SQL statement can allow SQL injection. Use parameterized queries instead.

30. Connecting Relational Databases with Python

Python applications commonly use relational databases to persist user accounts, product catalogs, transactions, application settings, and other structured information. Python includes the sqlite3 module for working with SQLite databases, while other database systems can be accessed through appropriate drivers or libraries.

Example: Using SQLite with Python

import sqlite3

connection = sqlite3.connect("college.db")

try:
    cursor = connection.cursor()

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS Students (
            StudentID INTEGER PRIMARY KEY,
            StudentName TEXT NOT NULL,
            Course TEXT NOT NULL
        )
    """)

    cursor.execute(
        "INSERT INTO Students (StudentName, Course) VALUES (?, ?)",
        ("Aarav Sharma", "Python")
    )

    connection.commit()

    cursor.execute(
        "SELECT StudentID, StudentName, Course FROM Students"
    )

    for student in cursor.fetchall():
        print(student)

except sqlite3.Error as error:
    connection.rollback()
    print("Database error:", error)

finally:
    connection.close()

This example creates or opens an SQLite database file, creates a table if necessary, inserts a record using parameterized SQL, commits the change, retrieves records, and closes the connection.

The question-mark placeholders (?) are SQLite parameter markers. Other database drivers may use different parameter styles, so always follow the driver's documentation.

Why Parameterized Queries Matter

Parameterized queries keep data values separate from SQL command text. They help prevent SQL injection and correctly handle values containing quotes or other special characters.

31. Practical Project: Student Management Database

Build a small database application that stores student details and their course enrollments.

Project Requirements

• Create a database for student records.
• Create a Students table with a primary key and required fields.
• Create a Courses table to store course information.
• Create an Enrollments table that connects students and courses.
• Insert sample students and courses.
• Retrieve all students and filter by course.
• Use joins to display student names with enrolled courses.
• Calculate the number of students enrolled in each course.
• Update a student's details and delete a test record safely.
• Connect the database to a simple Python program.

Example Query: Student Enrollment Report

SELECT
    Students.StudentName,
    Courses.CourseName
FROM Students
INNER JOIN Enrollments
    ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses
    ON Enrollments.CourseID = Courses.CourseID
ORDER BY Students.StudentName;

This query demonstrates how normalized tables can be joined to generate a readable report. It assumes that the tables contain the columns shown in the project requirements.

32. Best Practices for Relational Databases and SQL

• Give tables and columns clear, meaningful names.
• Choose suitable data types and define constraints deliberately.
• Use primary keys to identify records reliably.
• Use foreign keys to express and protect relationships.
• Normalize data where it reduces duplication and update anomalies.
• List required columns instead of using SELECT * in long-term application queries.
• Always review UPDATE and DELETE conditions before executing them.
• Use parameterized queries for application-supplied values.
• Use transactions for related operations that must remain consistent.
• Measure query performance before adding or changing indexes.
• Plan backups, access controls, and recovery procedures.
• Test database code against the specific database engine used in deployment.

33. Common Mistakes and Troubleshooting

34. Exercises for Practice

Beginner Exercises

1. Explain the difference between a database, a table, a row, and a column.
2. Create a database named LibraryDB using the syntax supported by your chosen database system.
3. Create a Books table with book ID, title, author, and price.
4. Insert five sample books into the table.
5. Retrieve all books and then retrieve only their titles and authors.
6. Filter books whose price is greater than 500.

Intermediate Exercises

1. Create a Departments table and an Employees table connected through a foreign key.
2. Write a query that retrieves employees belonging to the IT department.
3. Find the average salary for each department using GROUP BY.
4. Use HAVING to display departments whose average salary exceeds a specified amount.
5. Write an INNER JOIN and a LEFT JOIN between employees and departments.
6. Update an employee's department and safely delete a test record.

Advanced Beginner-to-Intermediate Exercises

1. Design a student-course database with a junction table for its many-to-many relationship.
2. Use a composite primary key to prevent duplicate student-course enrollments.
3. Write a query to count enrollments per course.
4. Explain how normalization prevents duplicated student details in enrollment records.
5. Connect a Python application to SQLite and implement create, read, update, and delete operations using parameterized queries.

35. Interview Questions and Answers

Q1. What is a relational database?

Answer: A relational database stores structured data in tables containing rows and columns. Relationships between tables are commonly represented using primary keys and foreign keys.

Q2. What is an RDBMS?

Answer: An RDBMS is a Relational Database Management System. It provides software for storing, retrieving, updating, and managing relational data, including constraints and transaction support.

Q3. What is SQL?

Answer: SQL stands for Structured Query Language. It is used to define database structures, retrieve data, modify records, and perform other database operations.

Q4. What is a primary key?

Answer: A primary key uniquely identifies each row in a table. Its values must be unique and cannot be NULL.

Q5. What is a foreign key?

Answer: A foreign key references a key in another or the same table and helps maintain valid relationships between records.

Q6. What is the difference between DELETE and DROP?

Answer: DELETE removes rows from a table, optionally using a condition. DROP TABLE removes the table object and its stored data.

Q7. What is the difference between WHERE and HAVING?

Answer: WHERE filters individual rows before grouping, while HAVING filters grouped results, often using aggregate functions.

Q8. What is a JOIN?

Answer: A join combines rows from related tables based on a join condition. Common types include inner, left, right, and cross joins.

Q9. What is normalization?

Answer: Normalization organizes data into related tables to reduce unnecessary duplication and help prevent inconsistent inserts, updates, and deletes.

Q10. What is an index?

Answer: An index is a database structure that can improve data retrieval for suitable queries, at the cost of extra storage and maintenance work.

Q11. What is a transaction?

Answer: A transaction is a logical unit of database operations that is managed as a single unit of work. Transactions help preserve consistency when operations must succeed or fail together.

Q12. What are ACID properties?

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe important guarantees of reliable transaction processing.

Q13. What is the difference between CHAR and VARCHAR?

Answer: CHAR is intended for fixed-length character data, while VARCHAR is intended for variable-length strings within a defined maximum in systems that support these types.

Q14. Why should SQL queries use parameters?

Answer: Parameterized queries separate input data from SQL command text. They help prevent SQL injection and handle special characters correctly.

Q15. How can Python connect to a relational database?

Answer: Python can use sqlite3 for SQLite and suitable database drivers for systems such as MySQL and PostgreSQL. Applications should use parameterized queries, handle errors, manage transactions, and close connections appropriately.

36. Quick Revision Checklist

• Understand relational databases, tables, rows, columns, and schemas.
• Know the difference between an RDBMS and SQL.
• Understand primary keys, foreign keys, and database relationships.
• Practice CREATE, INSERT, SELECT, UPDATE, and DELETE.
• Use WHERE, ORDER BY, GROUP BY, and HAVING correctly.
• Understand joins and when to use each type.
• Recognize common data types and integrity constraints.
• Understand normalization, indexes, transactions, and ACID properties.
• Use parameterized queries when connecting SQL to Python.
• Validate destructive operations and handle database errors safely.

37. Conclusion

Relational databases and SQL fundamentals are essential for developers who build applications that store and process structured information. Understanding tables, keys, relationships, constraints, queries, joins, aggregation, and transactions provides a strong foundation for working with real-world data.

For Python developers, these skills are particularly valuable when building web applications, automation tools, reporting systems, and data-driven software. Practice SQL statements using a database such as SQLite, MySQL, or PostgreSQL, and build a small project to apply these concepts in a realistic setting.

Explore the Python Training Course at JustAcademy, visit the JustAcademy homepage, or register for a course demo to learn more about available training opportunities.